package DBAccess;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.bean.WorkerBean;

/**
 * Title: Testdb Description: simple hello world db example of a standalone
 * persistent db application
 * 
 * every time it runs it adds four more rows to sample_table it does a query and
 * prints the results to standard out
 * 
 * Author: Karl Meissner karl@meissnersd.com
 */
public class DBAccess {
	static Connection conn; // our connnection to the db - presist for life of
							// program
	static ArrayList<WorkerBean> WorkerList =new ArrayList<WorkerBean>();

	// we dont want this garbage collected until we are done
	public DBAccess(String db_file_name_prefix) throws Exception { // Not using
																	// here. //
																	// exception

		// Load the HSQL Database Engine JDBC driver
		// hsqldb.jar should be in the class path or made part of the current
		// jar
		Class.forName("org.hsqldb.jdbc.JDBCDriver");

		// connect to the database. This will load the db files and start the
		// database if it is not already running.
		// db_file_name_prefix is used to open or create files that hold the
		// state
		// of the db.
		// It can contain directory names relative to the
		// current working directory

		conn = DriverManager.getConnection(
				"jdbc:hsqldb:" + db_file_name_prefix, // filenames
				"SA", // username
				""); // password

	}

	public static void start() throws Exception { // using here

		// Load the HSQL Database Engine JDBC driver
		// hsqldb.jar should be in the class path or made part of the current
		// jar
		Class.forName("org.hsqldb.jdbc.JDBCDriver");

		// connect to the database. This will load the db files and start the
		// database if it is not already running.
		// db_file_name_prefix is used to open or create files that hold the
		// state
		// of the db.
		// It can contain directory names relative to the
		// current working directory

		conn = DriverManager.getConnection("jdbc:hsqldb:mydb", // filenames
				"SA", // username
				""); // password

	}

	public static void shutdown() throws SQLException {

		Statement st = conn.createStatement();

		// db writes out to files and performs clean shuts down
		// otherwise there will be an unclean shutdown
		// when program ends
		st.execute("SHUTDOWN");
		conn.close(); // if there are no other open connection
	}

	// use for SQL command SELECT
	public synchronized static ArrayList<WorkerBean> query(String expression)
			throws SQLException {
		try {
			start();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		Statement st = null;
		ResultSet rs = null;

		st = conn.createStatement(); // statement objects can be reused with
		rs = st.executeQuery(expression); // run the query

		// do something with the result set.
		WorkerList.clear();
		dump(rs);
		st.close(); // NOTE!! if you close a statement the associated ResultSet
					// is
		shutdown();
		return WorkerList;
	}

	// use for SQL commands CREATE, DROP, INSERT and UPDATE
	public synchronized static void update(String expression)
			throws SQLException {
		try {
			start();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		Statement st = null;
		st = conn.createStatement(); // statements
		int i = st.executeUpdate(expression); // run the query
		if (i == -1) {
			System.out.println("db error : " + expression);
		} else
			System.out.println("Query Successful ! ");
		st.close();
		shutdown();
	} // void update()

	public static void dump(ResultSet rs) throws SQLException {

//		 ResultSetMetaData meta   = rs.getMetaData();
//	        int               colmax = meta.getColumnCount();
//	        int               i;
//	        Object            o = null;
	        int index=0;
		for (; rs.next();) {

//            for (i = 0; i < colmax; ++i) {
//                o = rs.getObject(i + 1);    // Is SQL the first column is indexed
//
//                // with 1 not 0
//                System.out.print(o.toString() + " ");
//            }
//
//            System.out.println(" ");

			String name,jobTitle,address,phone,emp_id=null;
			Date hireDate=null;
			emp_id=(String)rs.getObject("emp_id");
			name=(String)rs.getObject("name");
			jobTitle=(String)rs.getObject("jobTitle");
			address=(String)rs.getObject("address");
			phone=(String)rs.getObject("phone");
			hireDate=(Date)rs.getObject("hireDate");
			
			WorkerBean holderBean = new WorkerBean(emp_id,name, jobTitle, address, phone, hireDate);
			//WorkerList.add(holderBean);
			System.out.println("Found One ! ");
			WorkerList.add(index, holderBean);
			index++;
			
		}
	}

}// class DBAccess.java

